﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Collections.Specialized;

namespace DBSourceToolsLib.SysObjects
{
    public class QueryGenerator
    {
        // TODO : COMPLETE 1.2.0.8 generate update statements by primary key. (billhuber)
        // TODO : COMPLETE 1.2.0.8 generate select statements from all fields (billhuber)
        // TODO : COMPLETE 1.2.0.8 generate insert statements
        public string GenerateQuery(SourceDatabase oSource, QueryType oType)
        {
            switch (oType)
            {
                case QueryType.NewQuery:
                    return "";
                    break;
                case QueryType.CountDataQuery:
                    return GenerateCountQuery(oSource);
                    break;
                case QueryType.NewTableQuery:
                    return GenerateNewTableQuery(oSource);
                case QueryType.NewProc:
                    return GenerateNewProcQuery(oSource);
                case QueryType.NewFunction:
                    return GenerateNewFunction(oSource);
                default:
                    return "";
                    break;
            }
            
        }

        private string GenerateNewFunction(SourceDatabase oSource)
        {
            StringBuilder oBuilder = new StringBuilder();
            oBuilder.Append(GetHeader());

            oBuilder.Append(@"
CREATE FUNCTION /*function_name*/
(
/*    @IntField [int], */
/*    @VarcharField [varchar](50) */
)
RETURNS 
-- scalar function
/*    [int]     */
-- table valued function
/*    TABLE     */
AS
BEGIN
	-- scalar function
/*    DECLARE @ResultInt [int] */
/*    SELECT @ResultInt = 1 */
/*    RETURN @ResultInt */

    -- table valued function
/*    SELECT * from table_name */

END
GO
");

            return oBuilder.ToString();
        }

        private string GenerateNewProcQuery(SourceDatabase oSource)
        {
            StringBuilder oBuilder = new StringBuilder();
            oBuilder.Append(GetHeader());
            oBuilder.AppendLine("CREATE PROCEDURE /*Procedure_Name */");
            oBuilder.AppendLine("/*    @IntField [int], */");
            oBuilder.AppendLine("/*    @VarcharField [varchar](50) */");

            oBuilder.AppendLine("AS");
            oBuilder.AppendLine("BEGIN");
            oBuilder.AppendLine("   -- SET NOCOUNT ON added to prevent extra result sets from");
            oBuilder.AppendLine("	-- interfering with SELECT statements.");
            oBuilder.AppendLine("	SET NOCOUNT ON;");
            oBuilder.AppendLine("");
            oBuilder.AppendLine("    -- Insert statements for procedure here");
            oBuilder.AppendLine("	SELECT @IntField");
            oBuilder.AppendLine("END");
            oBuilder.AppendLine("GO");

            return oBuilder.ToString();
            
        }

        private string GenerateNewTableQuery(SourceDatabase oSource)
        {
            StringBuilder oBuilder = new StringBuilder();
            oBuilder.Append(GetHeader());

            oBuilder.Append(@"
CREATE TABLE [dbo].[/*tablename*/](
/* datatypes : */
/*    [big_int_col] [bigint] NULL, */
/*    [binary_col] [binary](50) NULL, */
/*    [bit_col] [nchar](10) NULL, */
/*    [char_col] [char](10) NULL, */
/*    [datetime_col] [datetime] NULL, */
/*    [decimal_col] [decimal](18, 0) NULL, */
/*    [float_col] [float] NULL, */
/*    [image_col] [image] NULL, */
/*    [int_col] [int] NULL, */
/*    [money_col] [money] NULL, */
/*    [nchar_col] [nchar](10) NULL, */
/*    [ntext_col] [ntext] NULL, */
/*    [numeric_col] [numeric](18, 0) NULL, */
/*    [nvarchar_col] [nvarchar](max) NULL, */
/*    [real_col] [real] NULL, */
/*    [smalldatetime_col] [smalldatetime] NULL, */
/*    [smallint_col] [smallint] NULL, */
/*    [smallmoney_col] [smallmoney] NULL, */
/*    [sql_variant_col] [sql_variant] NULL, */
/*    [text_col] [text] NULL, */
/*    [timestamp_col] [timestamp] NULL, */
/*    [tinyint_col] [tinyint] NULL, */
/*    [unique_col] [uniqueidentifier] NULL, */
/*    [varbinary_col] [varbinary](max) NULL, */
/*    [varchar_col] [varchar](max) NULL, */
/*    [xml_col] [xml] NULL */
    CONSTRAINT [PK_/*PrimaryKey] PRIMARY KEY ( [/*PrimaryKeyField*/] ASC )
) ON [PRIMARY] 

GO
");
            return oBuilder.ToString();
        }

        private string GetHeader()
        {
            StringBuilder oBuilder = new StringBuilder();
            DateHelper oDateHelper = new DateHelper();
            oBuilder.AppendLine("-- =============================================");
            oBuilder.AppendLine("-- Author:		" + System.Security.Principal.WindowsIdentity.GetCurrent().Name);

            oBuilder.AppendLine("-- Create date: " + oDateHelper.GetCurrentDateFormatted());
            oBuilder.AppendLine("-- =============================================");
            return oBuilder.ToString();
        }

        private string GenerateCountQuery(SourceDatabase oSource)
        {
            StringBuilder oBuilder = new StringBuilder();
            //oBuilder.Append(GetHeader());
            bool bNeedUnion = false;
            foreach (SOTable oTable in oSource.SysObjects.Tables)
            {
                if (bNeedUnion)
                    oBuilder.Append(" union ");
                oBuilder.AppendLine("select '" + oTable.Name + 
                    "' as TableName, count(*) as RecordCount from " + oTable.Name);
                bNeedUnion = true;
            }
            return oBuilder.ToString();
        }

        public string GenerateQuery(SourceDatabase oSourceDatabase, DBSourceToolsLib.Commands.DatabaseCommandTypes m_QueryType, string m_Params)
        {
            StringBuilder oBuilder = new StringBuilder();
            //oBuilder.Append(GetHeader());
            // params will contain the new database name;
            switch (m_QueryType) {
                case Commands.DatabaseCommandTypes.NewQueryScriptInsert:
                    oBuilder.Append(GenerateNewInsertScript(oSourceDatabase, m_Params));
                    break;
                case Commands.DatabaseCommandTypes.NewQueryScriptSelect:
                    oBuilder.Append(GenerateNewSelectScript(oSourceDatabase, m_Params));
                    break;
                case Commands.DatabaseCommandTypes.NewView:
                    oBuilder.Append(GenerateNewViewScript(oSourceDatabase));
                    break;
                case Commands.DatabaseCommandTypes.NewQuerySelectView:
                    oBuilder.Append(GenerateNewViewSelectScript(oSourceDatabase, m_Params));
                    break;
                case Commands.DatabaseCommandTypes.NewQueryUpdateTable:
                    oBuilder.Append(GenerateNewUpdateTable(oSourceDatabase, m_Params));
                    break;
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewDatabase:
                    {
                        if (oSourceDatabase != null)
                        {
                            Microsoft.SqlServer.Management.Smo.Server oServer = oSourceDatabase.GetSmoServer();
                            Microsoft.SqlServer.Management.Smo.Database oDatabase = oSourceDatabase.GetSmoDatabase(oServer);
                            Microsoft.SqlServer.Management.Smo.ScriptingOptions oScriptingOptions = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
                            oScriptingOptions.ExtendedProperties = UserSettings.Instance().UseExtendedProperties;
                            oScriptingOptions.AnsiFile = UserSettings.Instance().UseANSII;

                            StringCollection myStrColl = new StringCollection();
                            //oScriptingOptions.FileName = this.ScriptFilePath;
                            
                            myStrColl = oDatabase.Script(oScriptingOptions);
                            foreach (string strLine in myStrColl)
                            {
                                if (strLine.Contains(oSourceDatabase.DatabaseName))
                                {
                                    oBuilder.AppendLine(strLine.Replace(oSourceDatabase.DatabaseName, m_Params));
                                }
                                else
                                {
                                    oBuilder.AppendLine(strLine);
                                }
                            }

                            
                        }

                    }
                    break;
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewTableFromExcel:
                    {
                        oBuilder.AppendLine("CREATE TABLE [dbo].[/*[new_table_name]*/](");

                        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                        + m_Params + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

                        System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(connectionString);
                        oConn.Open();

                        using (System.Data.OleDb.OleDbDataAdapter oAdapter
                            = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", oConn))
                        {

                            System.Data.DataSet oDs = new System.Data.DataSet();
                            oAdapter.Fill(oDs, "ExcelInfo");
                            //DataTable oTable = oDs.Tables[0];
                            int count = 0;
                            string strInsertCols = "(";
                            foreach (System.Data.DataColumn oCol in oDs.Tables[0].Columns)
                            {
                                string strColString = "";
                                if (count > 0)
                                {
                                    strColString += ",";
                                    strInsertCols += ",";

                                }
                                
                                strColString += "[" + oCol.ColumnName + "]";
                                strInsertCols += "[" + oCol.ColumnName + "]";
                                
                                switch (oCol.DataType.UnderlyingSystemType.ToString())
                                {
                                    case "System.String":
                                        strColString += (" [varchar] (250) NULL");
                                        break;
                                    case "System.Double":
                                        strColString += (" [float] NULL");
                                        break;
                                    case "System.DateTime":
                                        strColString += (" [datetime] NULL");
                                        break;
                                    default:
                                        strColString += (" [undefined_data_type] ");
                                        break;
                                }
                                count++;
                                oBuilder.AppendLine(strColString);
                                //"string","double","datetime";
                            }
                            oBuilder.AppendLine(")");
                            strInsertCols += ")";
                            oBuilder.AppendLine();
                            oBuilder.AppendLine();

                            foreach (System.Data.DataRow oRow in oDs.Tables[0].Rows)
                            {
                                string strInsertString = "insert into [/*[new_table_name]*/] " + strInsertCols;
                                oBuilder.AppendLine(strInsertString);
                                string strInsertValues = " values (";

                                count = 0;
                                foreach (System.Data.DataColumn oCol in oDs.Tables[0].Columns)
                                {
                                    if (count > 0)
                                    {
                                        strInsertValues += ",";
                                    }

                                    switch (oCol.DataType.UnderlyingSystemType.ToString())
                                    {
                                        case "System.DateTime":
                                            DateTime oTime = DateTime.Parse(oRow[oCol.ColumnName].ToString());
                                            strInsertValues += "'" + oTime.ToString("dd MMM yyyy HH:mm:ss") + "'";
                                            break;
                                        case "System.String":
                                            string strValue = oRow[oCol.ColumnName].ToString();
                                            strValue = strValue.Replace("'", "''");
                                            strInsertValues += "'" + strValue + "'";
                                            break;
                                        default:

                                            strInsertValues += (String.IsNullOrEmpty(oRow[oCol.ColumnName].ToString()) ?
                                                "null" : oRow[oCol.ColumnName].ToString());
                                            break;
                                    }
                                    count++;
                                    
                                    //"string","double","datetime";
                                }
                                strInsertValues += ")";
                                oBuilder.AppendLine(strInsertValues);
                            }

                        }
                    }
                    break;
                default:
                    break;
            }
            return oBuilder.ToString();
        }

        private string GenerateNewUpdateTable(SourceDatabase oSourceDatabase, string m_Params)
        {
            StringBuilder oBuilder = new StringBuilder();
            StringBuilder oValueListBuilder = new StringBuilder();
            string strTableName = GetTableNameFromParams(m_Params);
            SOTable oTable = (SOTable)oSourceDatabase.SysObjects.getSysObject("Tables", strTableName);
            if (oTable != null)
            {
                oBuilder.AppendLine("update " + strTableName + " set ");
                oValueListBuilder.AppendLine("where ");
                bool bUpdateNeedComma = false;
                bool bWhereNeedAnd = false;

                foreach (ColInfo oColInfo in oTable.Columns)
                {
                    string strUpdate = "\t";
                    string strWhere = "\t";
                    if ((oColInfo.IsIdentity) || (oColInfo.PrimaryKey))
                    {
                        if (bWhereNeedAnd)
                            strWhere += "and ";
                        else
                            strWhere += "\t";
                        bWhereNeedAnd = true;

                        strWhere += " " + oColInfo.Name + " = ";
                        if (oColInfo.NeedsQuotes())
                            strWhere += "''";
                        oValueListBuilder.AppendLine(strWhere);
                    }
                    else
                    {
                        if (bUpdateNeedComma)
                        {
                            strUpdate += ",";
                            
                        }
                        bUpdateNeedComma = true;
                        strUpdate += "[" + oColInfo.Name + "] = ";
                        if (oColInfo.NeedsQuotes())
                            strUpdate += "''";
                        oBuilder.AppendLine(strUpdate);
                    }
                    
                    
                }

                oBuilder.Append(oValueListBuilder.ToString());

            }
            return oBuilder.ToString();

        }

        private string GenerateNewViewSelectScript(SourceDatabase oSourceDatabase, string m_Params)
        {
            StringBuilder oBuilder = new StringBuilder();
            string strTableName = GetTableNameFromParams(m_Params);
            SOView oView = (SOView)oSourceDatabase.SysObjects.getSysObject("Views", strTableName);
            if (oView != null)
            {
                oBuilder.Append(oView.GetSelectQuery());

            }
            return oBuilder.ToString();
        }


        private string GenerateNewSelectScript(SourceDatabase oSourceDatabase, string m_Params)
        {
            StringBuilder oBuilder = new StringBuilder();
            string strTableName = GetTableNameFromParams(m_Params);
            SOTable oTable = (SOTable)oSourceDatabase.SysObjects.getSysObject("Tables", strTableName);
            if (oTable != null)
            {
                oBuilder.Append(oTable.GetSelectQuery(null, true));

            }
            return oBuilder.ToString();
        }

        private string GetTableNameFromParams(string m_Params)
        {
            string strTableName = m_Params.Substring(m_Params.LastIndexOf('\\') + 1);
            strTableName = strTableName.Substring(0, strTableName.LastIndexOf('.'));
            return strTableName;
        }

        private string GenerateNewInsertScript(SourceDatabase oSourceDatabase, string m_Params)
        {
            StringBuilder oBuilder = new StringBuilder();
            StringBuilder oValueListBuilder = new StringBuilder();
            // findtablename
            string strTableName = GetTableNameFromParams(m_Params);

            SOTable oTable = (SOTable)oSourceDatabase.SysObjects.getSysObject("Tables", strTableName);
            
            if (oTable != null)
            {
                oBuilder.AppendLine("insert into " + strTableName + "(");
                oValueListBuilder.AppendLine("values (");
                bool bNeedComma = false;
                foreach (ColInfo oColInfo in oTable.Columns)
                {
                    string strInsert = "\t";
                    string strValues = "\t";
                    if (oColInfo.IsIdentity)
                    {
                        strInsert += "/*[" + oColInfo.Name + "] " + oColInfo.Type;
                        strInsert += (oColInfo.PrimaryKey ? " primary key " : "") + " (identity) */";
                        strValues += strInsert;
                    } else
                    {
                        if (bNeedComma)
                        {
                            strInsert += ",";
                            strValues += ",";
                        }
                        strInsert += "[" + oColInfo.Name + "]";
                        strInsert += (oColInfo.PrimaryKey ? " /* primary key */" : "");
                        strInsert += (oColInfo.IsNullable ? "" : " /* NOT NULL */");

                        if (oColInfo.IsDateTime())
                            strValues += "GETDATE()";
                        else
                            if (oColInfo.IsUniqueIdentifier())
                                strValues += "NEWID()";
                            else
                            {
                                if (oColInfo.NeedsQuotes())
                                    strValues += "''";
                            }
                        strValues += "\t/*" + oColInfo.Name + " ";
                        strValues += (oColInfo.PrimaryKey ? " primary key " : "");
                        strValues += (oColInfo.IsNullable ? "" : " NOT NULL ");
                        strValues += oColInfo.Type + " */";

                        bNeedComma = true;
                    }
                    oBuilder.AppendLine(strInsert);
                    oValueListBuilder.AppendLine(strValues);
                    

                }

                oValueListBuilder.AppendLine(")");
                oBuilder.AppendLine(")");
                oBuilder.Append(oValueListBuilder.ToString());

            }
            return oBuilder.ToString();
        }

        internal string GenerateQuery(SourceDatabase databaseEx, DBSourceToolsLib.Commands.DatabaseCommandTypes m_CommandType)
        {
            switch (m_CommandType)
            {
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewQuery:
                    return "";
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.CountDataQuery:
                    return GenerateCountQuery(databaseEx);
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewTableQuery:
                    return GenerateNewTableQuery(databaseEx);
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewProc:
                    return GenerateNewProcQuery(databaseEx);
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewFunction:
                    return GenerateNewFunction(databaseEx);
                default:
                    return "";
            }
        }

        private string GenerateNewViewScript(SourceDatabase oSourceDatabase)
        {
            StringBuilder oBuilder = new StringBuilder();
            oBuilder.AppendLine("CREATE VIEW /* ViewName */");
            oBuilder.AppendLine("AS");
            return oBuilder.ToString();
        }

    }
}
